﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using TarreOA.TarreOAModels;

namespace TarreOA.TarreOADAL
{
    public class AfficheInformtbDAO
    {
        /// <summary>
        /// 新建公告通知信息
        /// </summary>
        /// <param name="aiTitle"></param>
        /// <param name="aiContent"></param>
        /// <param name="aiCreateTime"></param>
        /// <param name="aiStartTime"></param>
        /// <param name="aiEndTime"></param>
        /// <param name="aiAffixFilePath"></param>
        /// <param name="aiOpenBound"></param>
        /// <param name="UserId"></param>
        /// <returns></returns>
        public int[] saveAfficheInformInfo(string aiTitle, string aiContent, DateTime aiCreateTime, DateTime aiStartTime, DateTime aiEndTime,
            string aiAffixFilePath, string aiOpenBound, int userId)
        {
            int[] counts = new int[2];
            SqlConnection sqlConn = null;
            SqlCommand sqlCmd = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "insert into AfficheInformtb values(@aiTitle, @aiContent, @aiCreateTime, @aiStartTime, @aiEndTime, @aiAffixFilePath, @aiOpenBound, @UserId, @aiFinishUsersid, @aiUnFinishUsersid)";
                sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@aiTitle", aiTitle));
                sqlCmd.Parameters.Add(new SqlParameter("@aiContent", aiContent));
                sqlCmd.Parameters.Add(new SqlParameter("@aiCreateTime", aiCreateTime));
                sqlCmd.Parameters.Add(new SqlParameter("@aiStartTime", aiStartTime));
                sqlCmd.Parameters.Add(new SqlParameter("@aiEndTime", aiEndTime));
                sqlCmd.Parameters.Add(new SqlParameter("@aiAffixFilePath", aiAffixFilePath));
                sqlCmd.Parameters.Add(new SqlParameter("@aiOpenBound", aiOpenBound));
                sqlCmd.Parameters.Add(new SqlParameter("@UserId", userId));
                UserDAO userDAO = new UserDAO();
                string[] sectionids = aiOpenBound.Split(',');
                string unfinishUsersid = ",";
                string finishUsersid = ",";
                for(int i = 0; i < sectionids.Length; i++) {
                    if(sectionids[i] != null && !"".Equals(sectionids[i])) {
                        List<User> users = userDAO.findByUserSectionId(int.Parse(sectionids[i]));
                        foreach(User user in users) {
                            unfinishUsersid += (user.UserId.ToString() + ",");
                        }
                    }
                }
                sqlCmd.Parameters.Add(new SqlParameter("@aiFinishUsersid", finishUsersid));
                sqlCmd.Parameters.Add(new SqlParameter("@aiUnFinishUsersid", unfinishUsersid));
                counts[0] = sqlCmd.ExecuteNonQuery();
                sql = "select @@identity";
                sqlCmd = new SqlCommand(sql, sqlConn);
                counts[1] = int.Parse(sqlCmd.ExecuteScalar().ToString());
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return counts;
        }

        /// <summary>
        /// 根据公告通知编号查找公告通知信息
        /// </summary>
        /// <param name="ai_id"></param>
        /// <returns></returns>
        public AfficheInformtb findById(int ai_id)
        {
            AfficheInformtb ai = null;
            SqlConnection sqlConn = null;
            SqlCommand sqlCmd = null;
            SqlDataReader sqlDr = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "select * from AfficheInformtb where AfficheInformId = @ai_id";
                sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@ai_id", ai_id));
                sqlDr = sqlCmd.ExecuteReader();
                if (sqlDr.HasRows)
                {
                    UserDAO userDAO = new UserDAO();
                    sqlDr.Read();
                    ai = new AfficheInformtb();
                    ai.AfficheInformId = (int)sqlDr["AfficheInformId"];
                    ai.AfficheInformTitle = (string)sqlDr["AfficheInformTitle"];
                    ai.AfficheInformContent = (string)sqlDr["AfficheInformContent"];
                    ai.AfficheInformCreateTime = (DateTime)sqlDr["AfficheInformCreateTime"];
                    ai.AfficheInformStartTime = (DateTime)sqlDr["AfficheInformStartTime"];
                    ai.AfficheInformEndTime = (DateTime)sqlDr["AfficheInformEndTime"];
                    ai.AfficheInformAffixFilePath = (string)sqlDr["AfficheInformAffixFilePath"];
                    ai.AfficheInformOpenBound = (string)sqlDr["AfficheInformOpenBound"];
                    ai.AfficheInformSendUser = userDAO.FindByUserId((int)sqlDr["AfficheInformSendUser"]);
                    ai.AfficheInformFinishUsersid = (string) sqlDr["AfficheInformFinishUsersid"];
                    ai.AfficheInformUnFinishUsersid = (string) sqlDr["AfficheInformUnFinishUsersid"];
                    string[] finishUsersid = ((string) sqlDr["AfficheInformFinishUsersid"]).Split(',');
                    string[] unfinishUsersid = ((string) sqlDr["AfficheInformUnFinishUsersid"]).Split(',');
                    List<User> finishUsers = new List<User>();
                    List<User> unfinishUsers = new List<User>();
                    if(finishUsersid != null && finishUsersid.Length > 0) {
                        for(int i = 0; i < finishUsersid.Length; i++) {
                            if(finishUsersid[i] != null && !"".Equals(finishUsersid[i])) {
                                finishUsers.Add(userDAO.FindByUserId(int.Parse(finishUsersid[i])));
                            }
                        }
                    }
                    if(unfinishUsersid != null && unfinishUsersid.Length > 0) {
                        for(int i = 0; i < unfinishUsersid.Length; i++) {
                            if(unfinishUsersid[i] != null && !"".Equals(unfinishUsersid[i])) {
                                unfinishUsers.Add(userDAO.FindByUserId(int.Parse(unfinishUsersid[i])));
                            }
                        }
                    }
                    ai.AfficheInformFinishUsers = finishUsers;
                    ai.AfficheInformUnFinishUsers = unfinishUsers;
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlDr != null)
                {
                    sqlDr.Close();
                    sqlDr.Dispose();
                }
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return ai;
        }

        /// <summary>
        /// 根据公告通知编号更改公告通知信息
        /// </summary>
        /// <param name="aiTitle"></param>
        /// <param name="aiContent"></param>
        /// <param name="aiCreateTime"></param>
        /// <param name="aiStartTime"></param>
        /// <param name="aiEndTime"></param>
        /// <param name="aiAffixFilePath"></param>
        /// <param name="aiOpenBound"></param>
        /// <param name="aiid"></param>
        /// <returns></returns>
        public int updateAfficheInformInfo(string aiTitle, string aiContent, DateTime aiCreateTime, DateTime aiStartTime, DateTime aiEndTime,
            string aiAffixFilePath, string aiOpenBound, int aiid)
        {
            int count = 0;
            SqlConnection sqlConn = null;
            SqlCommand sqlCmd = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "update AfficheInformtb set AfficheInformTitle = @aiTitle, AfficheInformContent = @aiContent, AfficheInformCreateTime = @aiCreateTime, AfficheInformStartTime = @aiStartTime"
                    + ", AfficheInformEndTime = @aiEndTime, AfficheInformAffixFilePath = @aiAffixFilePath, AfficheInformOpenBound = @aiOpenBound where AfficheInformId = @aiid";
                sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@aiTitle", aiTitle));
                sqlCmd.Parameters.Add(new SqlParameter("@aiContent", aiContent));
                sqlCmd.Parameters.Add(new SqlParameter("@aiCreateTime", aiCreateTime));
                sqlCmd.Parameters.Add(new SqlParameter("@aiStartTime", aiStartTime));
                sqlCmd.Parameters.Add(new SqlParameter("@aiEndTime", aiEndTime));
                sqlCmd.Parameters.Add(new SqlParameter("@aiAffixFilePath", aiAffixFilePath));
                sqlCmd.Parameters.Add(new SqlParameter("@aiOpenBound", aiOpenBound));
                sqlCmd.Parameters.Add(new SqlParameter("@aiid", aiid));
                count = sqlCmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return count;
        }

        /// <summary>
        /// 根据公告通知编号删除公告通知信息
        /// </summary>
        /// <param name="aiid"></param>
        /// <returns></returns>
        public int delAfficheInformInfoById(int aiid)
        {
            int count = 0;
            SqlConnection sqlConn = null;
            SqlCommand sqlCmd = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "delete from AfficheInformtb where AfficheInformId = @aiid";
                sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@aiid", aiid));
                count = sqlCmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return count;
        }

        /// <summary>
        /// 查找所有公告通知信息
        /// </summary>
        /// <returns></returns>
        public List<AfficheInformtb> findALL()
        {
            List<AfficheInformtb> list = null;
            SqlConnection sqlConn = null;
            SqlCommand sqlCmd = null;
            SqlDataReader sqlDr = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "select * from AfficheInformtb";
                sqlCmd = new SqlCommand(sql, sqlConn);
                sqlDr = sqlCmd.ExecuteReader();
                if (sqlDr.HasRows)
                {
                    list = new List<AfficheInformtb>();
                    UserDAO userDAO = new UserDAO();
                    while(sqlDr.Read())
                    {
                        AfficheInformtb ai = new AfficheInformtb();
                        ai.AfficheInformId = (int)sqlDr["AfficheInformId"];
                        ai.AfficheInformTitle = (string)sqlDr["AfficheInformTitle"];
                        ai.AfficheInformContent = (string)sqlDr["AfficheInformContent"];
                        ai.AfficheInformCreateTime = (DateTime)sqlDr["AfficheInformCreateTime"];
                        ai.AfficheInformStartTime = (DateTime)sqlDr["AfficheInformStartTime"];
                        ai.AfficheInformEndTime = (DateTime)sqlDr["AfficheInformEndTime"];
                        ai.AfficheInformAffixFilePath = (string)sqlDr["AfficheInformAffixFilePath"];
                        ai.AfficheInformOpenBound = (string)sqlDr["AfficheInformOpenBound"];
                        ai.AfficheInformSendUser = userDAO.FindByUserId((int)sqlDr["AfficheInformSendUser"]);
                        list.Add(ai);
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlDr != null)
                {
                    sqlDr.Close();
                    sqlDr.Dispose();
                }
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return list;
        }

        /// <summary>
        /// 查找指定日期内的公告通知
        /// </summary>
        /// <param name="startDate"></param>
        /// <returns></returns>
        public List<AfficheInformtb> findBetweenStartDateAndEndDate(DateTime startDate)
        {
            List<AfficheInformtb> list = null;
            SqlConnection sqlConn = null;
            SqlCommand sqlCmd = null;
            SqlDataReader sqlDr = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "select * from AfficheInformtb where AfficheInformStartTime = @startDate";
                sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@startDate", startDate));
                sqlDr = sqlCmd.ExecuteReader();
                if (sqlDr.HasRows)
                {
                    list = new List<AfficheInformtb>();
                    UserDAO userDAO = new UserDAO();
                    while (sqlDr.Read())
                    {
                        AfficheInformtb ai = new AfficheInformtb();
                        ai.AfficheInformId = (int)sqlDr["AfficheInformId"];
                        ai.AfficheInformTitle = (string)sqlDr["AfficheInformTitle"];
                        ai.AfficheInformContent = (string)sqlDr["AfficheInformContent"];
                        ai.AfficheInformCreateTime = (DateTime)sqlDr["AfficheInformCreateTime"];
                        ai.AfficheInformStartTime = (DateTime)sqlDr["AfficheInformStartTime"];
                        ai.AfficheInformEndTime = (DateTime)sqlDr["AfficheInformEndTime"];
                        ai.AfficheInformAffixFilePath = (string)sqlDr["AfficheInformAffixFilePath"];
                        ai.AfficheInformOpenBound = (string)sqlDr["AfficheInformOpenBound"];
                        ai.AfficheInformSendUser = userDAO.FindByUserId((int)sqlDr["AfficheInformSendUser"]);
                        list.Add(ai);
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlDr != null)
                {
                    sqlDr.Close();
                    sqlDr.Dispose();
                }
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return list;
        }

        /// <summary>
        /// 根据公告通知编号让该通知立即终止
        /// </summary>
        /// <param name="ai_id"></param>
        /// <returns></returns>
        public int toStopByAiid(int ai_id) {
            int count = 0;
            SqlConnection sqlConn = null;
            SqlCommand sqlCmd = null;
            try {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "update AfficheInformtb set AfficheInformEndTime = @endTime where AfficheInformId = @ai_id";
                sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@endTime", DateTime.Now.Date));
                sqlCmd.Parameters.Add(new SqlParameter("@ai_id", ai_id));
                count = sqlCmd.ExecuteNonQuery();
            } catch(Exception ex) {
                Console.WriteLine(ex.Message);
            } finally {
                if(sqlConn != null && sqlConn.State == ConnectionState.Open) {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return count;
        }

        /// <summary>
        /// 根据公告通知编号让该通知恢复生效
        /// </summary>
        /// <param name="ai_id"></param>
        /// <returns></returns>
        public int toGoOnByAiid(int ai_id) {
            int count = 0;
            SqlConnection sqlConn = null;
            SqlCommand sqlCmd = null;
            try {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "update AfficheInformtb set AfficheInformEndTime = @endTIme where AfficheInformId = @ai_id";
                sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@endTime", DateTime.Now.Date.AddYears(1)));
                sqlCmd.Parameters.Add(new SqlParameter("@ai_id", ai_id));
                count = sqlCmd.ExecuteNonQuery();
            } catch(Exception ex) {
                Console.WriteLine(ex.Message);
            } finally {
                if(sqlConn != null && sqlConn.State == ConnectionState.Open) {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return count;
        }

        /// <summary>
        /// 跟据当前用户所属部门查看对应的公告通知
        /// </summary>
        /// <param name="usid"></param>
        /// <returns></returns>
        public List<AfficheInformtb> findByUser(int usid) {
            List<AfficheInformtb> list = null;
            SqlConnection sqlConn = null;
            SqlCommand sqlCmd = null;
            SqlDataReader sqlDr = null;
            try {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "select * from AfficheInformtb where AfficheInformEndTime > @now and (AfficheInformOpenBound like @sid or AfficheInformOpenBound = '0') order by [AfficheInformCreateTime] desc";
                sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@now", DateTime.Now.Date));
                sqlCmd.Parameters.Add(new SqlParameter("@sid", "%," + usid + ",%"));
                sqlDr = sqlCmd.ExecuteReader();
                if(sqlDr.HasRows) {
                    list = new List<AfficheInformtb>();
                    UserDAO userDAO = new UserDAO();
                    while(sqlDr.Read()) {
                        AfficheInformtb ai = new AfficheInformtb();
                        ai.AfficheInformId = (int) sqlDr["AfficheInformId"];
                        ai.AfficheInformTitle = (string) sqlDr["AfficheInformTitle"];
                        ai.AfficheInformContent = (string) sqlDr["AfficheInformContent"];
                        ai.AfficheInformCreateTime = (DateTime) sqlDr["AfficheInformCreateTime"];
                        ai.AfficheInformStartTime = (DateTime) sqlDr["AfficheInformStartTime"];
                        ai.AfficheInformEndTime = (DateTime) sqlDr["AfficheInformEndTime"];
                        ai.AfficheInformAffixFilePath = (string) sqlDr["AfficheInformAffixFilePath"];
                        ai.AfficheInformOpenBound = (string) sqlDr["AfficheInformOpenBound"];
                        ai.AfficheInformSendUser = userDAO.FindByUserId((int) sqlDr["AfficheInformSendUser"]);
                        ai.AfficheInformFinishUsersid = (string) sqlDr["AfficheInformFinishUsersid"];
                        ai.AfficheInformUnFinishUsersid = (string) sqlDr["AfficheInformUnFinishUsersid"];
                        //string[] finishUsersid = ((string) sqlDr["AfficheInformFinishUsersid"]).Split(',');
                        //string[] unfinishUsersid = ((string) sqlDr["AfficheInformUnFinishUsersid"]).Split(',');
                        //List<User> finishUsers = new List<User>();
                        //List<User> unfinishUsers = new List<User>();
                        //if(finishUsersid != null && finishUsersid.Length > 0) {
                        //    for(int i = 0; i < finishUsersid.Length; i++) {
                        //        if(finishUsersid[i] != null && !"".Equals(finishUsersid[i])) {
                        //            finishUsers.Add(userDAO.FindByUserId(int.Parse(finishUsersid[i])));
                        //        }
                        //    }
                        //}
                        //if(unfinishUsersid != null && unfinishUsersid.Length > 0) {
                        //    for(int i = 0; i < unfinishUsersid.Length; i++) {
                        //        if(unfinishUsersid[i] != null && !"".Equals(unfinishUsersid[i])) {
                        //            unfinishUsers.Add(userDAO.FindByUserId(int.Parse(unfinishUsersid[i])));
                        //        }
                        //    }
                        //}
                        //ai.AfficheInformFinishUsers = finishUsers;
                        //ai.AfficheInformUnFinishUsers = unfinishUsers;
                        list.Add(ai);
                    }
                }
            } catch(Exception ex) {
                Console.WriteLine(ex.Message);
            } finally {
                if(sqlDr != null) {
                    sqlDr.Close();
                    sqlDr.Dispose();
                }
                if(sqlConn != null && sqlConn.State == ConnectionState.Open) {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return list;
        }

        /// <summary>
        /// 根据公告通知编号更改该通知的已阅读和未阅读用户的状态
        /// </summary>
        /// <param name="ai_id"></param>
        /// <param name="finishUsersid"></param>
        /// <param name="unfinishUsersid"></param>
        /// <returns></returns>
        public int updateFinishAndUnFinishUsersid(int ai_id, string finishUsersid, string unfinishUsersid) {
            int count = 0;
            SqlConnection sqlConn = null;
            SqlCommand sqlCmd = null;
            try {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "update AfficheInformtb set AfficheInformFinishUsersid = @finishUsersid, AfficheInformUnFinishUsersid = @unfinishUsersid where AfficheInformId = @ai_id";
                sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@finishUsersid", finishUsersid));
                sqlCmd.Parameters.Add(new SqlParameter("@unfinishUsersid", unfinishUsersid));
                sqlCmd.Parameters.Add(new SqlParameter("@ai_id", ai_id));
                count = sqlCmd.ExecuteNonQuery();
            } catch(Exception ex) {
                Console.WriteLine(ex.Message);
            } finally {
                if(sqlConn != null && sqlConn.State == ConnectionState.Open) {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return count;
        }
    }
}
